const mysql = require('mysql2/promise');

const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs'
};

async function migrateAddMessageType() {
    let connection;

    try {
        console.log('🔌 连接到数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 检查表是否存在
        console.log('🔍 检查private_messages表是否存在...');
        const [tables] = await connection.execute(
            "SHOW TABLES LIKE 'private_messages'"
        );

        if (tables.length === 0) {
            console.log('❌ private_messages表不存在，请先创建表');
            return;
        }

        console.log('✅ private_messages表存在');

        // 检查messageType字段是否已存在
        console.log('🔍 检查messageType字段是否已存在...');
        const [columns] = await connection.execute(
            "SHOW COLUMNS FROM `private_messages` LIKE 'messageType'"
        );

        if (columns.length > 0) {
            console.log('ℹ️ messageType字段已存在，跳过添加');
        } else {
            // 添加messageType字段
            console.log('📋 添加messageType字段...');
            await connection.execute(`
                ALTER TABLE \`private_messages\` 
                ADD COLUMN \`messageType\` tinyint NOT NULL DEFAULT 1 
                COMMENT '消息类型 (1:玩家消息, 2:系统消息)' 
                AFTER \`status\`
            `);
            console.log('✅ messageType字段添加成功');
        }

        // 更新现有数据的messageType字段
        console.log('📝 更新现有数据的messageType字段...');
        const [updateResult] = await connection.execute(`
            UPDATE \`private_messages\` 
            SET \`messageType\` = 1 
            WHERE \`messageType\` IS NULL OR \`messageType\` = 0
        `);
        console.log(`✅ 更新了 ${updateResult.affectedRows} 条记录`);

        // 验证字段添加结果
        console.log('🔍 验证字段添加结果...');
        const [columnInfo] = await connection.execute(`
            SELECT 
                COLUMN_NAME,
                DATA_TYPE,
                IS_NULLABLE,
                COLUMN_DEFAULT,
                COLUMN_COMMENT
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'private_messages' 
            AND COLUMN_NAME = 'messageType'
        `);

        if (columnInfo.length > 0) {
            const col = columnInfo[0];
            console.log('📊 messageType字段信息:');
            console.log(`  字段名: ${col.COLUMN_NAME}`);
            console.log(`  数据类型: ${col.DATA_TYPE}`);
            console.log(`  是否可空: ${col.IS_NULLABLE}`);
            console.log(`  默认值: ${col.COLUMN_DEFAULT}`);
            console.log(`  注释: ${col.COLUMN_COMMENT}`);
        }

        // 验证数据更新结果
        console.log('🔍 验证数据更新结果...');
        const [dataStats] = await connection.execute(`
            SELECT 
                COUNT(*) as total_messages,
                SUM(CASE WHEN messageType = 1 THEN 1 ELSE 0 END) as player_messages,
                SUM(CASE WHEN messageType = 2 THEN 1 ELSE 0 END) as system_messages
            FROM \`private_messages\`
        `);

        if (dataStats.length > 0) {
            const stats = dataStats[0];
            console.log('📊 数据统计:');
            console.log(`  总消息数: ${stats.total_messages}`);
            console.log(`  玩家消息数: ${stats.player_messages}`);
            console.log(`  系统消息数: ${stats.system_messages}`);
        }

        // 显示表结构
        console.log('🔍 显示表结构...');
        const [tableStructure] = await connection.execute('DESCRIBE `private_messages`');
        console.log('📊 表结构:');
        tableStructure.forEach(row => {
            console.log(`  ${row.Field}: ${row.Type} ${row.Null === 'NO' ? 'NOT NULL' : 'NULL'} ${row.Default ? `DEFAULT ${row.Default}` : ''} ${row.Comment ? `COMMENT '${row.Comment}'` : ''}`);
        });

        console.log('🎉 迁移完成！messageType字段已成功添加');

    } catch (error) {
        console.error('❌ 迁移失败:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 如果直接运行此脚本
if (require.main === module) {
    migrateAddMessageType()
        .then(() => {
            console.log('✅ 脚本执行完成');
            process.exit(0);
        })
        .catch((error) => {
            console.error('❌ 脚本执行失败:', error);
            process.exit(1);
        });
}

module.exports = { migrateAddMessageType };
